unit SQLFormat;

interface

uses Windows, SysUtils, variants;

type
  TSQLF = class
  public
    class function Date(const Date: TDateTime): string; overload;
    class function Date(const Date: String): string; overload;
    class function Money(const Money: Currency): string;
    class function Int(const Int: integer): string;
    class function Text(const str: string): string;
    class function Float(const Float: string): string; overload;
    class function Float(const Float: Double): string; overload;
    class function EscapeSpecialChars(const str: String): string;
    class function Boolean(const Bool: Boolean): String; overload;
    class function Boolean(const Bool: String): String; overload;
  end;

implementation

uses StringMangler;

var
  fs: TFormatSettings;

class function TSQLF.Date(const Date: TDateTime): string;
// var
// Year, Month, Day, hour,min,sec,mili : Word;
begin
  // DecodeDateTime(Date, Year, Month, Day,hour,min,sec,mili) ;
  // SysUtils.DateSeparator := '.' ;
  // SysUtils.ShortDateFormat := 'dd.mm.yyyy';

  Result := 'Convert(datetime,';
  Result := Result + QuotedStr
    (FormatDateTime('mm"/"dd"/"yyyy hh":"mm":"ss', Date));
  Result := Result + ',101)';
end;

class function TSQLF.Boolean(const Bool: Boolean): String;
begin
  if (Bool) then
  begin
    Result := '1';
  end
  else
  begin
    Result := '0';
  end;
end;

class function TSQLF.Boolean(const Bool: String): String;
begin
  Result := 'NULL';
  if (Trim(Bool) <> '') then
  begin
    if ((LowerCase(Bool) = 'true') OR (Trim(Bool) = '1')) then
    begin
      Result := '1';
    end
    else if ((LowerCase(Bool) = 'false') OR (Trim(Bool) = '0')) then
    begin
      Result := '0';
    end;
  end;
end;

class function TSQLF.Date(const Date: String): string;
var
  fDate: TDateTime;
begin
  Result := 'NULL';
  if (Date <> '') AND (Date <> '0') then
  begin
    fDate := VarToDateTime(Date);
    Result := 'Convert(datetime, ';
    Result := Result + QuotedStr
      (FormatDateTime('mm"/"dd"/"yyyy hh":"mm":"ss', fDate));
    Result := Result + ', 101)';
  end;
end;

class function TSQLF.EscapeSpecialChars(const str: String): string;
var
  tmpStr: String;
  i: integer;
begin
  tmpStr := 'NULL';

  if (str <> '') then
  begin
    tmpStr := '';
    for i := 1 to Length(str) do
    begin
      if (str[i] <> '''') then
      begin
        tmpStr := tmpStr + str[i];
      end
      else
      begin
        tmpStr := tmpStr + '''''';
      end;

    end;
    tmpStr := '''' + tmpStr + '''';
  end;
  Result := tmpStr;
end;

class function TSQLF.Float(const Float: Double): string;
begin
  Result := TStringMangler.FloatToDotStr(Float);
end;

class function TSQLF.Float(const Float: string): string;
begin
  Result := 'NULL';
  if (Float <> '') then
  begin
    Result := TStringMangler.FloatToDotStr(Float);
    Result := TStringMangler.replaceAllChars(Result, ' ', '', false);
    Result := TStringMangler.replaceAllChars(Result, chr(160), '', false);
  end;
end;

class function TSQLF.Int(const Int: integer): string;
begin
  Result := IntToStr(Int);
end;

class function TSQLF.Money(const Money: Currency): string;
var
  decSep: char;
begin
  decSep := fs.DecimalSeparator;
  fs.DecimalSeparator := '.';

  Result := CurrToStr(Money, fs);

  fs.DecimalSeparator := decSep;
end;

class function TSQLF.Text(const str: string): string;
begin
  Result := QuotedStr(str);
end;

initialization

TFormatSettings.Create(LOCALE_SYSTEM_DEFAULT);
//GetLocaleFormatSettings(LOCALE_SYSTEM_DEFAULT, fs);

finalization

// nothing special in the finalization

end.
